﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PBOffice.Models;
using System.Data;
using System.Data.SqlClient;

namespace PBOffice.DAL
{
    public class RoleStaffService
    {
        public List<RoleStaff> GetRoleStfByRoleId(int id)
        {
            string sql = string.Format("select s.StfId,s.StfName,r.* from Staff s,RoleStaff r where s.StfId=r.StfId and r.RoleId={0}", id);
            List<RoleStaff> list = new List<RoleStaff>();
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    RoleStaff role = new RoleStaff();
                    Staff staff = new Staff();
                    staff.StfId = (int)dr["StfId"];
                    staff.StfName = dr["StfName"].ToString();
                    role.staff = staff;
                    role.RoleId = (int)dr["RoleId"];
                    role.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
                    list.Add(role);

                }
            }
            return list;
        }
        public void DeleteRoleStaff(int roleId)
        {
            string sql = string.Format("delete [RoleStaff] where RoleId={0}", roleId);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
        public void AddRoleStaff(RoleStaff role)
        {
            string sql = "insert [RoleStaff] (StfId,RoleId,CreateTime) values(@StfId,@RoleId,@CreateTime)";
            SqlParameter[] spa = {
                                     new SqlParameter("@StfId",role.staff.StfId),
                                     new SqlParameter("@RoleId",role.RoleId),
                                     new SqlParameter("@CreateTime",role.CreateTime)
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
    }
}
